﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DrSoftsFrame.Servers;
using System.Data.SqlClient;
using PreeRegisterModel;

namespace PreeRegisterDAL
{
    public class IdentityGetDAL
    {
        /// <summary>
        /// 获取诊疗ID信息  
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public static DataSet IdentityGet(IdentityGetRequestModel identityGetRequestModel)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "up_insertOPDPatient_ALL";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@cPatientName", SqlDbType.VarChar, 20);
                p1.Direction = ParameterDirection.Input;
                p1.Value = identityGetRequestModel.PatientName;
                cmd.Parameters.Add(p1);

                SqlParameter p2 = new SqlParameter("@cSex", SqlDbType.VarChar, 10);
                p2.Direction = ParameterDirection.Input;
                p2.Value = identityGetRequestModel.Sex;
                cmd.Parameters.Add(p2);

                SqlParameter p3 = new SqlParameter("@cAddress", SqlDbType.VarChar, 50);
                p3.Direction = ParameterDirection.Input;
                p3.Value = identityGetRequestModel.Address;
                cmd.Parameters.Add(p3);


                SqlParameter p4 = new SqlParameter("@cRelationType", SqlDbType.VarChar, 20);
                p4.Direction = ParameterDirection.Input;
                p4.Value = identityGetRequestModel.tel;
                cmd.Parameters.Add(p4);

                SqlParameter p5 = new SqlParameter("@dBirthday", SqlDbType.VarChar, 20);
                p5.Direction = ParameterDirection.Input;
                p5.Value = identityGetRequestModel.Birthday;
                cmd.Parameters.Add(p5);

                SqlParameter p6 = new SqlParameter("@cIdentityCard", SqlDbType.VarChar, 20);
                p6.Direction = ParameterDirection.Input;
                p6.Value = identityGetRequestModel.identityCard;
                cmd.Parameters.Add(p6);

                SqlParameter p7 = new SqlParameter("@iPatientTypeID", SqlDbType.Int);
                p7.Direction = ParameterDirection.Input;
                p7.Value = 1;
                cmd.Parameters.Add(p7);

                SqlParameter p8 = new SqlParameter("@dNearUsedDate", SqlDbType.DateTime);
                p8.Direction = ParameterDirection.Input;
                p8.Value = DateTime.Now;
                cmd.Parameters.Add(p8);

                SqlParameter p9 = new SqlParameter("@cSpellCode", SqlDbType.VarChar, 10);
                p9.Direction = ParameterDirection.Input;
                p9.Value = "";
                cmd.Parameters.Add(p9);

                SqlParameter p10 = new SqlParameter("@cMedicalCode", SqlDbType.VarChar, 10);
                p10.Direction = ParameterDirection.Input;
                p10.Value = "";
                cmd.Parameters.Add(p10);

                SqlParameter p12 = new SqlParameter("@cICCardCode", SqlDbType.VarChar, 20);
                p12.Direction = ParameterDirection.Input;
                p12.Value = "";
                cmd.Parameters.Add(p12);

                SqlParameter p13 = new SqlParameter("@cDiagnoseCardCode", SqlDbType.VarChar, 20);
                p13.Direction = ParameterDirection.Input;
                p13.Value = "";
                cmd.Parameters.Add(p13);

                SqlParameter p14 = new SqlParameter("@iYearsUnit", SqlDbType.Int);
                p14.Direction = ParameterDirection.Input;
                p14.Value = identityGetRequestModel.ageunit;
                cmd.Parameters.Add(p14);

                SqlParameter p15 = new SqlParameter("@iYearsOld", SqlDbType.Int);
                p15.Direction = ParameterDirection.Input;
                p15.Value = identityGetRequestModel.age;
                cmd.Parameters.Add(p15);

                SqlParameter p11 = new SqlParameter("@cRelationName", SqlDbType.VarChar, 20);
                p11.Direction = ParameterDirection.Input;
                p11.Value = "";
                cmd.Parameters.Add(p11);

                SqlParameter p16 = new SqlParameter("@cRelationPhone", SqlDbType.VarChar, 20);
                p16.Direction = ParameterDirection.Input;
                p16.Value = "";
                cmd.Parameters.Add(p16);

                SqlParameter p18 = new SqlParameter("@cDrugSensitive", SqlDbType.VarChar, 20);
                p18.Direction = ParameterDirection.Input;
                p18.Value = "";
                cmd.Parameters.Add(p18);

                ClinicDb mydb = new ClinicDb();
                DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
                if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1)
                {
                    return null;
                }
                return ds;

            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 获取就诊号  liujia151120
        /// </summary>
        /// <param name="identityGetRequestModel"></param>
        /// <returns></returns>
        public static DataSet GetPatientCode(IdentityGetRequestModel identityGetRequestModel)
        {
            try
            {
                string sqlstr = string.Format(@"	    
                                                select top 1 
		                                        [iPatientID],
		                                        [cPatientName]=RTRIM(LTRIM([cPatientName])),
		                                        [cSex],
		                                        [cAddress],
		                                        [cRelationType],
		                                        [bValidFlag],
		                                        [dBirthday],
		                                        [cIdentityCard],
		                                        [iPatientTypeID],
		                                        [cTypeCode],
		                                        [cPatientCode]=RTRIM(LTRIM([cPatientCode])),
		                                        [dCreateDate],
		                                        [dNearUsedDate],
		                                        [cSpellCode],
		                                        [cMedicalCode]=RTRIM(LTRIM([cMedicalCode])),
		                                        [cDiagnoseCardCode]=RTRIM(LTRIM([cDiagnoseCardCode])),
		                                        [iYearsUnit],
		                                        [iYearsOld],
                                                cCardNum=''                   --增加诊疗卡身编号查询   liujia151203
		                                        from OPDPatient
		                                        where cIdentityCard='{0}' and RTRIM(LTRIM(cPatientName))='{1}'  --增加患者姓名条件  liujia151218
		                                        order by dCreateDate desc", identityGetRequestModel.identityCard, identityGetRequestModel.PatientName.Trim());
                ClinicDb mydb = new ClinicDb();
                DataSet ds = mydb.ExecuteSelect(sqlstr);
                if (ds == null || ds.Tables.Count < 1)
                {
                    return null;
                }
                return ds;

            }
            catch (Exception err)
            {

                throw;
            }
        }

        /// <summary>
        /// 获取诊疗卡账户号  liujia151125
        /// </summary>
        /// <param name="identityGetRequestModel"></param>
        /// <returns></returns>
        public static DataSet GetZLKCardCode(IdentityGetRequestModel identityGetRequestModel)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "up_GetZLKCardCode_interPay";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@cIdentityCard", SqlDbType.VarChar, 30);
                p1.Direction = ParameterDirection.Input;
                p1.Value = identityGetRequestModel.identityCard;
                cmd.Parameters.Add(p1);

                //增加患者姓名传入  liujia151218
                SqlParameter p2 = new SqlParameter("@cName", SqlDbType.VarChar, 30);
                p2.Direction = ParameterDirection.Input;
                p2.Value = identityGetRequestModel.PatientName;
                cmd.Parameters.Add(p2);

                ZlCardDb mydb = new ZlCardDb();
                DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
                if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1)
                {
                    return null;
                }
                return ds;

            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 创建就诊号  liujia151120
        /// </summary>
        /// <returns></returns>
        public static int CreateNewPatientCode()
        {
            ClinicDb mydb = new ClinicDb();
            try
            {
                int PatientCode = 0;

                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "sp_getNewPatientCode";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@isystemid", SqlDbType.Int);
                p1.Direction = ParameterDirection.Output;
                p1.Value = PatientCode;
                cmd.Parameters.Add(p1);

                object[] ob = mydb.ExecuteSqlCmdWithOutParmList(cmd);
                PatientCode = Convert.ToInt32(ob[0]);
                return PatientCode;
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 获取诊疗卡账户流水号  liujia151120
        /// </summary>
        /// <param name="Userid"></param>
        /// <returns></returns>
        public static string CreateZLKFlowCode(string Userid)
        {
            ZlCardDb mydb = new ZlCardDb();
            try
            {
                int FlowCode = 0;

                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "sp_zlk_getZLkCodeFlow";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@UserId", SqlDbType.VarChar,20);
                p1.Direction = ParameterDirection.Input;
                p1.Value = Userid;
                cmd.Parameters.Add(p1);

                SqlParameter p2 = new SqlParameter("@iFlowCode", SqlDbType.Int);
                p2.Direction = ParameterDirection.Output;
                p2.Value = FlowCode;
                cmd.Parameters.Add(p2);

                object[] ob = mydb.ExecuteSqlCmdWithOutParmList(cmd);
                FlowCode = Convert.ToInt32(ob[0]);

                return FlowCode.ToString();
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 生成患者ID  liujia151120
        /// </summary>
        /// <returns></returns>
        public static int CreatePatientid(ref int ReturnValue, ref string ls_error)
        {
            ClinicDb mydb = new ClinicDb();
            try
            {
                int PatientId = 0;

                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "up_createpatientid_batch";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@li_increaseid", SqlDbType.Int);
                p1.Direction = ParameterDirection.Input;
                p1.Value = 1;
                cmd.Parameters.Add(p1);

                SqlParameter p2 = new SqlParameter("@li_patientid", SqlDbType.Int);
                p2.Direction = ParameterDirection.Output;
                p2.Value = PatientId;
                cmd.Parameters.Add(p2);

                SqlParameter p3 = new SqlParameter("@ls_error", SqlDbType.Char,50);
                p3.Direction = ParameterDirection.Output;
                p3.Value = ls_error;
                cmd.Parameters.Add(p3);

                SqlParameter p4 = new SqlParameter("@ReturnValue", SqlDbType.Int);
                p4.Direction = ParameterDirection.ReturnValue;
                p4.Value = ReturnValue;
                cmd.Parameters.Add(p4);

                object[] ob = mydb.ExecuteSqlCmdWithOutParmList(cmd);
                PatientId = Convert.ToInt32(ob[0]);
                ls_error = ob[1].ToString();
                ReturnValue = Convert.ToInt32(ob[2]);

                return PatientId;
            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 插入HIS患者表记录  liujia151121
        /// </summary>
        /// <param name="identityGetRequestModel"></param>
        /// <param name="cpatientcode"></param>
        /// <param name="iPatientID"></param>
        /// <returns></returns>
        public static DataSet InsertHisOpdpatient(IdentityGetRequestModel identityGetRequestModel,string cpatientcode,int iPatientID)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "up_insertOPDPatient_ALL_New";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@cPatientName", SqlDbType.VarChar, 20);
                p1.Direction = ParameterDirection.Input;
                p1.Value = identityGetRequestModel.PatientName;
                cmd.Parameters.Add(p1);

                SqlParameter p2 = new SqlParameter("@cSex", SqlDbType.VarChar, 10);
                p2.Direction = ParameterDirection.Input;
                p2.Value = identityGetRequestModel.Sex;
                cmd.Parameters.Add(p2);

                SqlParameter p3 = new SqlParameter("@cAddress", SqlDbType.VarChar, 50);
                p3.Direction = ParameterDirection.Input;
                p3.Value = identityGetRequestModel.Address;
                cmd.Parameters.Add(p3);


                SqlParameter p4 = new SqlParameter("@cRelationType", SqlDbType.VarChar, 20);
                p4.Direction = ParameterDirection.Input;
                p4.Value = identityGetRequestModel.tel;
                cmd.Parameters.Add(p4);

                SqlParameter p5 = new SqlParameter("@dBirthday", SqlDbType.VarChar, 20);
                p5.Direction = ParameterDirection.Input;
                p5.Value = identityGetRequestModel.Birthday;
                cmd.Parameters.Add(p5);

                SqlParameter p6 = new SqlParameter("@cIdentityCard", SqlDbType.VarChar, 20);
                p6.Direction = ParameterDirection.Input;
                p6.Value = identityGetRequestModel.identityCard;
                cmd.Parameters.Add(p6);

                SqlParameter p7 = new SqlParameter("@iPatientTypeID", SqlDbType.Int);
                p7.Direction = ParameterDirection.Input;
                p7.Value = 1;
                cmd.Parameters.Add(p7);

                SqlParameter p8 = new SqlParameter("@dNearUsedDate", SqlDbType.DateTime);
                p8.Direction = ParameterDirection.Input;
                p8.Value = DateTime.Now;
                cmd.Parameters.Add(p8);

                SqlParameter p9 = new SqlParameter("@cSpellCode", SqlDbType.VarChar, 10);
                p9.Direction = ParameterDirection.Input;
                p9.Value = "";
                cmd.Parameters.Add(p9);

                SqlParameter p10 = new SqlParameter("@cMedicalCode", SqlDbType.VarChar, 10);
                p10.Direction = ParameterDirection.Input;
                p10.Value = "";
                cmd.Parameters.Add(p10);

                SqlParameter p12 = new SqlParameter("@cICCardCode", SqlDbType.VarChar, 20);
                p12.Direction = ParameterDirection.Input;
                p12.Value = "";
                cmd.Parameters.Add(p12);

                SqlParameter p13 = new SqlParameter("@cDiagnoseCardCode", SqlDbType.VarChar, 20);
                p13.Direction = ParameterDirection.Input;
                p13.Value = "";
                cmd.Parameters.Add(p13);

                SqlParameter p14 = new SqlParameter("@iYearsUnit", SqlDbType.Int);
                p14.Direction = ParameterDirection.Input;
                p14.Value = identityGetRequestModel.ageunit;
                cmd.Parameters.Add(p14);

                SqlParameter p15 = new SqlParameter("@iYearsOld", SqlDbType.Int);
                p15.Direction = ParameterDirection.Input;
                p15.Value = identityGetRequestModel.age;
                cmd.Parameters.Add(p15);

                SqlParameter p11 = new SqlParameter("@cRelationName", SqlDbType.VarChar, 20);
                p11.Direction = ParameterDirection.Input;
                p11.Value = "";
                cmd.Parameters.Add(p11);

                SqlParameter p16 = new SqlParameter("@cRelationPhone", SqlDbType.VarChar, 20);
                p16.Direction = ParameterDirection.Input;
                p16.Value = "";
                cmd.Parameters.Add(p16);

                SqlParameter p18 = new SqlParameter("@cDrugSensitive", SqlDbType.VarChar, 20);
                p18.Direction = ParameterDirection.Input;
                p18.Value = "";
                cmd.Parameters.Add(p18);

                SqlParameter p17 = new SqlParameter("@PatientId", SqlDbType.Int);
                p17.Direction = ParameterDirection.Input;
                p17.Value = iPatientID;
                cmd.Parameters.Add(p17);

                SqlParameter p19 = new SqlParameter("@PatientCode", SqlDbType.VarChar, 20);
                p19.Direction = ParameterDirection.Input;
                p19.Value = cpatientcode;
                cmd.Parameters.Add(p19);

                SqlParameter p20 = new SqlParameter("@userid", SqlDbType.VarChar, 10);
                p20.Direction = ParameterDirection.Input;
                p20.Value = identityGetRequestModel.userid;
                cmd.Parameters.Add(p20);

                ClinicDb mydb = new ClinicDb();
                DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
                if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1)
                {
                    return null;
                }
                return ds;

            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 创建诊疗卡账户  liujia151121
        /// </summary>
        /// <param name="identityGetRequestModel"></param>
        /// <param name="cpatientcode"></param>
        /// <param name="iPatientID"></param>
        /// <returns></returns>
        public static DataSet CreateZLKCard(IdentityGetRequestModel identityGetRequestModel, string cpatientcode, int iPatientID)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "up_CreateVirtualZlkCard_InterPay";
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter p1 = new SqlParameter("@BankNo", SqlDbType.VarChar, 30);
                p1.Direction = ParameterDirection.Input;
                p1.Value = "";
                cmd.Parameters.Add(p1);

                SqlParameter p2 = new SqlParameter("@IdentityCard", SqlDbType.VarChar, 30);
                p2.Direction = ParameterDirection.Input;
                p2.Value = identityGetRequestModel.identityCard;
                cmd.Parameters.Add(p2);

                SqlParameter p3 = new SqlParameter("@PatientName", SqlDbType.VarChar, 30);
                p3.Direction = ParameterDirection.Input;
                p3.Value = identityGetRequestModel.PatientName;
                cmd.Parameters.Add(p3);


                SqlParameter p4 = new SqlParameter("@Sex", SqlDbType.VarChar, 30);
                p4.Direction = ParameterDirection.Input;
                p4.Value = identityGetRequestModel.Sex;
                cmd.Parameters.Add(p4);

                SqlParameter p5 = new SqlParameter("@Birthday", SqlDbType.VarChar, 30);
                p5.Direction = ParameterDirection.Input;
                p5.Value = identityGetRequestModel.Birthday;
                cmd.Parameters.Add(p5);

                SqlParameter p6 = new SqlParameter("@Age", SqlDbType.VarChar, 30);
                p6.Direction = ParameterDirection.Input;
                p6.Value = identityGetRequestModel.age;
                cmd.Parameters.Add(p6);

                SqlParameter p7 = new SqlParameter("@Address", SqlDbType.VarChar, 30);
                p7.Direction = ParameterDirection.Input;
                p7.Value = identityGetRequestModel.Address;
                cmd.Parameters.Add(p7);

                SqlParameter p8 = new SqlParameter("@Tel", SqlDbType.VarChar, 30);
                p8.Direction = ParameterDirection.Input;
                p8.Value = identityGetRequestModel.tel;
                cmd.Parameters.Add(p8);

                SqlParameter p9 = new SqlParameter("@Notes", SqlDbType.VarChar, 30);
                p9.Direction = ParameterDirection.Input;
                p9.Value = "";
                cmd.Parameters.Add(p9);

                SqlParameter p10 = new SqlParameter("@cSpellCode", SqlDbType.VarChar, 20);
                p10.Direction = ParameterDirection.Input;
                p10.Value = DrSoftsFrame.Common.ToChineseSpell(identityGetRequestModel.PatientName);
                cmd.Parameters.Add(p10);


                SqlParameter p11 = new SqlParameter("@iPatientID", SqlDbType.Int);
                p11.Direction = ParameterDirection.Input;
                p11.Value = iPatientID;
                cmd.Parameters.Add(p11);

                SqlParameter p12 = new SqlParameter("@UserID", SqlDbType.VarChar, 30);
                p12.Direction = ParameterDirection.Input;
                p12.Value = identityGetRequestModel.userid;
                cmd.Parameters.Add(p12);


                SqlParameter p13 = new SqlParameter("@CcardCode", SqlDbType.VarChar, 30);
                p13.Direction = ParameterDirection.Input;
                p13.Value = cpatientcode;
                cmd.Parameters.Add(p13);

                ZlCardDb mydb = new ZlCardDb();
                DataSet ds = mydb.ExecuteSqlCmdFillDs(cmd);
                if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1)
                {
                    return null;
                }
                return ds;

            }
            catch (Exception err)
            {
                throw err;
            }
        }

        /// <summary>
        /// 删除HIS患者记录
        /// </summary>
        /// <param name="cpatientcode"></param>
        /// <returns></returns>
        public static bool DelHISPatient(string cpatientcode)
        {
            try
            {
                string sqlstr = string.Format(@"delete from OPDPatient where cPatientCode='{0}'", cpatientcode);
                ClinicDb mydb = new ClinicDb();
                mydb.ExecuteSqlCmd(sqlstr);
                return true;
            }
            catch (Exception err)
            {

                throw;
            }
        }
    }
}
